#coding=utf-8
from framework.db.mysql import BaseDb
from tornado import gen
from lib import log

class Log(BaseDb):

    @gen.coroutine
    def select_log(self, school_id, where, page, offset):
        sql = '''SELECT Fid, Frepair_id, Frepair_name, Fuser_id
                , Fuser_name, Fuser_phone, Fdevice_type_id, Fdevice_type_name, Fdevice_id
                , Fdevice_name, Faddress, Fstatus, Fcreate_time
                , Fdorm_id, Fdorm_name
                FROM tb_repair_log 
                WHERE Fschool_id = %s %s 
                ORDER BY Fcreate_time DESC
                LIMIT %s, %s'''
        count_sql = 'SELECT count(a.Fid) FROM tb_repair_log a WHERE a.Fschool_id = %s %s'

        wheres = ''
        for i in where.keys():
            if where[i] != '':
                wheres += " AND " + i + "='" + str(where[i]) + "'"
        sql = sql % ('%s', wheres, '%s', '%s')
        count_sql = count_sql % ('%s', wheres)

        ret = yield self.executejson(sql, (school_id, page, offset))
        count = yield self.execute(count_sql, (school_id))

        raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def save(self, did, mainid):
        sql = 'UPDATE tb_repair_log SET Fstatus = 2, Frepair_id = %s, Frepair_name = %s WHERE Fid = %s'
        query = 'SELECT Fname FROM tb_repairman WHERE Fid = %s'

        Fname = yield self.execute(query, (mainid))
        yield self.execute(sql, (mainid, Fname[0][0], did))

        raise gen.Return()

    @gen.coroutine
    def finish(self, ids):
        sql = 'UPDATE tb_repair_log SET Fstatus = 3 WHERE Fid = %s'

        yield self.execute(sql, (ids))

        raise gen.Return()

    @gen.coroutine
    def search(self, school_id, student_name,tel,repairman_name,work_id,status,dorm_no, start, offset):
        resp = []
        sel_sql = []
        cnt_sql = []
        sel_list = []
        cnt_list = []

        sel_sql.append('''SELECT Fid, Frepair_id, Frepair_name, Fuser_id
                , Fuser_name, Fuser_phone, Fdevice_type_id, Fdevice_type_name, Fdevice_id
                , Fdevice_name, Faddress, Fstatus, Fcreate_time
                , Fdorm_id, Fdorm_name FROM tb_repair_log WHERE Fschool_id=%s ''')
        cnt_sql.append("SELECT COUNT(Fid) FROM tb_repair_log WHERE Fschool_id=%s")
        sel_list.append(school_id)
        cnt_list.append(school_id)
        if student_name != '':
            sel_sql.append(" AND Fuser_name=%s")
            cnt_sql.append(" AND Fuser_name=%s")
            sel_list.append(student_name)
            cnt_list.append(student_name)
        if tel != '':
            sel_sql.append(" AND Fuser_phone=%s")
            cnt_sql.append(" AND Fuser_phone=%s")
            sel_list.append(tel)
            cnt_list.append(tel)
        if repairman_name != '':
            sel_sql.append(" AND Frepair_name=%s")
            cnt_sql.append(" AND Frepair_name=%s")
            sel_list.append(repairman_name)
            cnt_list.append(repairman_name)
        if work_id != '':
            sel_sql.append(" AND Fdevice_type_id=%s")
            cnt_sql.append(" AND Fdevice_type_id=%s")
            sel_list.append(work_id)
            cnt_list.append(work_id)
        if status != '':
            sel_sql.append(" AND Fstatus=%s")
            cnt_sql.append(" AND Fstatus=%s")
            sel_list.append(status)
            cnt_list.append(status)
        if dorm_no != '':
            sel_sql.append(" AND Fdorm_id=%s")
            cnt_sql.append(" AND Fdorm_id=%s")
            sel_list.append(dorm_no)
            cnt_list.append(dorm_no)
        sel_sql.append(" ORDER BY Fid DESC LIMIT %s,%s")
        sel_list.append(start)
        sel_list.append(offset)
        sel_sql = " ".join(sel_sql)
        cnt_sql = " ".join(cnt_sql)
        sel_tpl = tuple(sel_list)
        cnt_tpl = tuple(cnt_list)
        try:
            sel_cur = yield self.executejson(sel_sql, sel_tpl)
            cnt_ret = yield self.execute(cnt_sql, cnt_tpl)
            resp = [cnt_ret[0][0], sel_cur]
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)